/* CREATE THE DATABASE */
USE [master]
GO
/****** Object:  Database [myAppSQL]    Script Date: 10/12/2007 12:24:13 ******/
CREATE DATABASE [myAppSQL] ON  PRIMARY 
( NAME = N'myAppSQL', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\myAppSQL.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'myAppSQL_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\myAppSQL_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'myAppSQL', @new_cmptlevel=90
GO



/* CREATE USERS TABLE */

USE [myAppSQL]
GO
/****** Object:  Table [dbo].[USERS]    Script Date: 10/12/2007 12:17:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[USERS](
        [User_ID] [int] IDENTITY(1,1) NOT NULL,
        [FirstName] [nvarchar](50) NULL,
        [LastName] [nvarchar](50) NULL,
        [Login] [nvarchar](50) NOT NULL,
        [Password] [nvarchar](200) NULL,
        [Passcrypt] [smallint] NULL CONSTRAINT [DF_USERS_Passcrypt]  DEFAULT ((0)),
        [DateCreated] [datetime] NULL,
        [DateTimeUpdated] [datetime] NULL,
        [LastLoginDateTime] [datetime] NULL,
        [UserIsDiscontinued] [bit] NULL CONSTRAINT [DF_USERS_UserIsDiscontinued]  DEFAULT ((0)),
 CONSTRAINT [PK_USERS] PRIMARY KEY CLUSTERED
(
        [User_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


/* CREATE GROUPS TABLE */


USE [myAppSQL]
GO
/****** Object:  Table [dbo].[GROUPS]    Script Date: 10/12/2007 12:19:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[GROUPS](
        [Group_ID] [int] IDENTITY(1,1) NOT NULL,
        [GroupName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_GROUPS] PRIMARY KEY CLUSTERED
(
        [Group_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO



/*	CREATE NOTES TABLES	*/


USE [myAppSQL]
GO
/****** Object:  Table [dbo].[NOTES]    Script Date: 10/12/2007 12:19:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NOTES](
        [Note_ID] [int] IDENTITY(1,1) NOT NULL,
        [NoteCreatedBy_ID] [int] NULL,
        [NoteSubject] [nvarchar](200) NULL,
        [NoteText] [nvarchar](4000) NULL,
        [NoteCreatedDateTime] [datetime] NULL,
 CONSTRAINT [PK_NOTES] PRIMARY KEY CLUSTERED
(
        [Note_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[NOTES]  WITH CHECK ADD  CONSTRAINT [FK_NOTES_USERS] FOREIGN KEY([NoteCreatedBy_ID])
REFERENCES [dbo].[USERS] ([User_ID])
GO
ALTER TABLE [dbo].[NOTES] CHECK CONSTRAINT [FK_NOTES_USERS]



/*	Add descrption to field names... this is generally a good idea.	*/

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Users first name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'USERS', @level2type=N'COLUMN',@level2name=N'FirstName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Users last name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'USERS', @level2type=N'COLUMN',@level2name=N'LastName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Users login name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'USERS', @level2type=N'COLUMN',@level2name=N'Login'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date this user account was created' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'USERS', @level2type=N'COLUMN',@level2name=N'DateCreated'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Last date time this user account was updated' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'USERS', @level2type=N'COLUMN',@level2name=N'DateTimeUpdated'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Users last login date and time' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'USERS', @level2type=N'COLUMN',@level2name=N'LastLoginDateTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Is this user account discontinued' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'USERS', @level2type=N'COLUMN',@level2name=N'UserIsDiscontinued'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Name of the group' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'GROUPS', @level2type=N'COLUMN',@level2name=N'GroupName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'FK:USERS references the user who created this note' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NOTES', @level2type=N'COLUMN',@level2name=N'NoteCreatedBy_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The subject of the note' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NOTES', @level2type=N'COLUMN',@level2name=N'NoteSubject'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The note message' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NOTES', @level2type=N'COLUMN',@level2name=N'NoteText'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time this note was created' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'NOTES', @level2type=N'COLUMN',@level2name=N'NoteCreatedDateTime'
